package com.jx.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.jx.dao.FindSomeDepDao;
import com.jx.model.Dep;
import com.jx.util.JDBCUtil;

public class FindSomeDepDaoImpl implements FindSomeDepDao{
	/**
	 * 根据查询条件查询部门
	 * @param dname
	 * @param start
	 * @param pagesize
	 * @return
	 */
	@Override
	public List<Dep> findSomeDepDao(String dname,int start,int pagesize) {
		// TODO Auto-generated method stub
		List<Dep> deps=new ArrayList<Dep>();
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("select * from j_dep where dname like ? limit ?,?");
		StringBuffer sql2=new StringBuffer("SELECT j_func.fname FROM dep_func,j_func WHERE j_func.fid=dep_func.fid and did=?");

		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1, "%"+dname+"%");
			pst.setInt(2, start);
			pst.setInt(3, pagesize);
			ResultSet res=pst.executeQuery();
			while(res.next()){
				String funcs="";
				PreparedStatement pst2=con.prepareStatement(sql2.toString());
				pst2.setInt(1, res.getInt("did"));
				ResultSet res2=pst2.executeQuery();
				while(res2.next()){
					funcs+=res2.getString("fname")+",";
				}
				Dep dep=new Dep(res.getInt("did"),res.getString("dname"),res.getString("ddes"),funcs);
				deps.add(dep);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return deps;
	}
	/**
	 * 根据查询条件查询出部门的总数
	 */
	@Override
	public int findTotalDep(String dname) {
		// TODO Auto-generated method stub
		int total=0;
		Connection con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("select count(did) dtotal from j_dep where dname like ?");
		try {
			PreparedStatement pst=con.prepareStatement(sql.toString());
			pst.setString(1, "%"+dname+"%");
			ResultSet res=pst.executeQuery();
			if(res.next()){
				total=res.getInt("dtotal");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

}
